﻿IF EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'OTDetalleSave')
	BEGIN
		DROP  Procedure  OTDetalleSave
	END
GO


CREATE PROCEDURE OTDetalleSave 
	(	
		@OTEntregaId AS INT,		
		@fecha AS DATETIME,		
		@operarioId AS INT = NULL,
		@ayudanteId AS INT = NULL,
		@maquinaId AS INT = NULL,
		@cantidad AS DECIMAL(10,2)  = NULL,
		@horasRegular AS DECIMAL(10,2)  = NULL,
		@horasExtra AS DECIMAL(10,2)  = NULL,
		@horasDoble AS DECIMAL(10,2)  = NULL,
		@tipo AS CHAR(1) = NULL,
		@descripcion AS VARCHAR(200)  = NULL
	)	
AS
BEGIN	
	SET NOCOUNT ON	
	
	-- "Constantes" con factores de costo de horas extra y doble
	DECLARE @FactorHoraExtra AS DECIMAL(10,2)
	DECLARE @FactorHoraDoble AS DECIMAL(10,2)
	SELECT	@FactorHoraExtra = 1.5, 
			@FactorHoraDoble = 2
			
	DECLARE @CostoAyudantexHora AS MONEY
	DECLARE @CostoOperarioxHora AS MONEY
	DECLARE @CostoMaquinaxHora AS MONEY
	
	DECLARE @CostoHorasRegular AS MONEY
	DECLARE @CostoHorasExtra AS MONEY
	DECLARE @CostoHorasDoble AS MONEY	
	DECLARE @CostoMaquina AS MONEY
	
	-- Extrae los costos por hora de las tablas catalogos	
	SELECT	@CostoAyudantexHora = CostoxHora FROM Lit_Operario WHERE Id = @ayudanteId	
	SELECT	@CostoOperarioxHora = CostoxHora FROM Lit_Operario WHERE Id = @operarioId	
	SELECT	@CostoMaquinaxHora  = CostoxHora FROM Lit_Maquina  WHERE Id = @maquinaId
		
	-- Calcula totales
	SET @CostoHorasRegular = ISNULL(@horasRegular * @CostoAyudantexHora,0) + 
							 ISNULL(@horasRegular * @CostoOperarioxHora,0)
								 
	SET @CostoHorasExtra =	 ISNULL(@horasExtra * @CostoAyudantexHora * @FactorHoraExtra, 0) + 
							 ISNULL(@horasExtra * @CostoOperarioxHora * @FactorHoraExtra, 0)
	
	SET @CostoHorasDoble =	 ISNULL(@horasDoble * @CostoAyudantexHora * @FactorHoraDoble, 0) + 
							 ISNULL(@horasDoble * @CostoOperarioxHora * @FactorHoraDoble, 0)
	
	SET @CostoMaquina = @CostoMaquinaxHora * (ISNULL(@horasRegular,0) + ISNULL(@horasExtra,0) + ISNULL(@horasDoble,0))
	
		
	INSERT INTO Lit_OTDetalle
		(OTEntregaId, Fecha, OperarioId, AyudanteId, MaquinaId, Cantidad, HorasRegular, HorasExtra, HorasDoble, Tipo, Descripcion,
		 CostoAyudantexHora, CostoOperarioxHora, CostoMaquinaxHora, CostoHorasRegular, CostoHorasExtra, CostoHorasDoble, CostoMaquina)
	VALUES 
		(@OTEntregaId, @fecha, @operarioId, @ayudanteId, @maquinaId, @cantidad, @horasRegular, @horasExtra, @horasDoble, @tipo, @descripcion,
		 @CostoAyudantexHora, @CostoOperarioxHora, @CostoMaquinaxHora, @CostoHorasRegular, @CostoHorasExtra, @CostoHorasDoble, @CostoMaquina) 
		
	SET NOCOUNT OFF
END
GO  